"""
Sperm Vials API routes - Sperm Vial Management
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from db.connection import execute_query

bp = Blueprint('sperm_vials', __name__, url_prefix='/api/vials')

@bp.route('', methods=['GET'])
def get_vials():
    """
    Get all sperm vials with donor information and filtering
    GET /api/vials?keyword=xxx&status=Available&page=1&pageSize=10
    """
    try:
        keyword = request.args.get('keyword', '').strip()
        status = request.args.get('status', '').strip()
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        
        query = """
            SELECT sv.vial_id, sv.collection_date, sv.motility_percent,
                   sv.sperm_count_mil, sv.volume_ml, sv.status,
                   sv.storage_location, sv.quarantine_release_date,
                   d.donor_id, d.donor_code, d.full_name as donor_name, d.blood_type,
                   -- 只有 Discarded 状态且从未被订单使用过的样本才能删除
                   (sv.status = 'Discarded' AND NOT EXISTS(
                       SELECT 1 FROM order_items WHERE vial_id = sv.vial_id
                   )) as can_delete
            FROM sperm_vials sv
            JOIN donors d ON sv.donor_id = d.donor_id
            WHERE 1=1
        """
        
        params = []
        
        # 关键词搜索（样本ID或捐精人信息）
        if keyword:
            query += " AND (CAST(sv.vial_id AS TEXT) ILIKE %s OR d.donor_code ILIKE %s OR d.full_name ILIKE %s)"
            search_pattern = f'%{keyword}%'
            params.extend([search_pattern, search_pattern, search_pattern])
        
        # 状态筛选
        if status:
            query += " AND sv.status = %s"
            params.append(status)
        
        query += " ORDER BY sv.vial_id ASC"
        
        # 分页
        offset = (page - 1) * page_size
        query += " LIMIT %s OFFSET %s"
        params.extend([page_size, offset])
        
        vials = execute_query(query, tuple(params) if params else None)
        
        # 获取总数
        count_query = """
            SELECT COUNT(*) as count
            FROM sperm_vials sv
            JOIN donors d ON sv.donor_id = d.donor_id
            WHERE 1=1
        """
        count_params = []
        
        if keyword:
            count_query += " AND (CAST(sv.vial_id AS TEXT) ILIKE %s OR d.donor_code ILIKE %s OR d.full_name ILIKE %s)"
            search_pattern = f'%{keyword}%'
            count_params.extend([search_pattern, search_pattern, search_pattern])
        
        if status:
            count_query += " AND sv.status = %s"
            count_params.append(status)
        
        count_result = execute_query(count_query, tuple(count_params) if count_params else None, fetch_one=True)
        total_count = count_result['count'] if count_result else 0
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': vials,
            'count': total_count
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:vial_id>', methods=['GET'])
def get_vial(vial_id):
    """
    Get a single vial by ID
    GET /api/sperm-vials/:id
    """
    try:
        query = """
            SELECT sv.*, d.donor_code, d.full_name as donor_name,
                   d.blood_type, d.ethnicity
            FROM sperm_vials sv
            JOIN donors d ON sv.donor_id = d.donor_id
            WHERE sv.vial_id = %s
        """
        
        vial = execute_query(query, (vial_id,), fetch_one=True)
        
        if not vial:
            return jsonify({
                'code': 404,
            'message': 'Vial not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': vial
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('', methods=['POST'])
def create_vial():
    """
    Create a new vial
    POST /api/sperm-vials
    """
    try:
        data = request.json
        
        required_fields = ['donor_id', 'collection_date']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'code': 400,
                    'message': f'Missing required field: {field}'
                }), 400
        
        query = """
            INSERT INTO sperm_vials (donor_id, collection_date, motility_percent,
                                    sperm_count_mil, volume_ml, status,
                                    storage_location, quarantine_release_date, notes)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            RETURNING vial_id, collection_date, status
        """
        
        params = (
            data['donor_id'],
            data['collection_date'],
            data.get('motility_percent'),
            data.get('sperm_count_mil'),
            data.get('volume_ml'),
            data.get('status', 'Available'),
            data.get('storage_location'),
            data.get('quarantine_release_date'),
            data.get('notes')
        )
        
        result = execute_query(query, params, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'Vial created successfully',
            'data': result
        }), 201
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:vial_id>', methods=['PUT'])
def update_vial(vial_id):
    """
    Update a vial
    PUT /api/vials/:id
    """
    try:
        data = request.json
        
        update_fields = []
        params = []
        
        allowed_fields = ['motility_percent', 'sperm_count_mil', 'volume_ml',
                         'status', 'storage_location', 'quarantine_release_date', 'notes']
        
        for field in allowed_fields:
            if field in data:
                update_fields.append(f"{field} = %s")
                params.append(data[field])
        
        if not update_fields:
            return jsonify({
                'code': 400,
            'message': 'No fields to update'
        }), 400
        
        params.append(vial_id)
        
        query = f"""
            UPDATE sperm_vials
            SET {', '.join(update_fields)}
            WHERE vial_id = %s
            RETURNING vial_id, status, storage_location
        """
        
        result = execute_query(query, tuple(params), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
            'message': 'Vial not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Vial updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:vial_id>', methods=['DELETE'])
def delete_vial(vial_id):
    """
    Delete a vial (hard delete - permanently remove from database)
    Only Discarded and Used vials can be deleted
    DELETE /api/vials/:id
    """
    try:
        # 先查询样本是否存在
        check_query = """
            SELECT vial_id, donor_id, collection_date, status
            FROM sperm_vials
            WHERE vial_id = %s
        """
        vial = execute_query(check_query, (vial_id,), fetch_one=True)
        
        if not vial:
            return jsonify({
                'code': 404,
                'message': 'Vial not found'
            }), 404
        
        # 检查状态：只有 Discarded 和 Used 可以删除
        if vial['status'] not in ('Discarded', 'Used'):
            status_text = {
                'Available': '可用',
                'Reserved': '已预订'
            }.get(vial['status'], vial['status'])
            return jsonify({
                'code': 400,
                'message': f'无法删除：该样本状态为"{status_text}"，只有已废弃或已使用的样本才能删除'
            }), 400
        
        # 检查是否被任何订单使用过（保留订单历史记录）
        usage_query = """
            SELECT COUNT(*) as count, 
                   STRING_AGG(CAST(o.order_id AS TEXT), ', ') as order_ids,
                   STRING_AGG(o.status, ', ') as order_statuses
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.order_id
            WHERE oi.vial_id = %s
        """
        usage_result = execute_query(usage_query, (vial_id,), fetch_one=True)
        
        if usage_result and usage_result['count'] > 0:
            return jsonify({
                'code': 400,
                'message': f'无法删除：该样本已被订单使用（订单ID: {usage_result["order_ids"]}），为保留订单历史记录不可删除。如需处理问题样本，请使用"废弃"功能。'
            }), 400
        
        # 物理删除样本（只有从未被订单使用过的样本才能删除）
        delete_query = """
            DELETE FROM sperm_vials
            WHERE vial_id = %s
        """
        execute_query(delete_query, (vial_id,), fetch=False)
        
        return jsonify({
            'code': 200,
            'message': 'Vial deleted successfully',
            'data': vial
        }), 200
        
    except Exception as e:
        error_message = str(e)
        # 检查是否是外键约束错误
        if 'foreign key' in error_message.lower() or 'violates' in error_message.lower():
            return jsonify({
                'code': 400,
                'message': '无法删除：该样本存在关联数据，请先删除相关记录'
            }), 400
        return jsonify({
            'code': 500,
            'message': error_message
        }), 500

@bp.route('/available', methods=['GET'])
def get_available_vials():
    """
    Get all available vials
    GET /api/sperm-vials/available
    """
    try:
        query = """
            SELECT sv.vial_id, sv.collection_date, sv.motility_percent,
                   sv.sperm_count_mil, sv.volume_ml, sv.storage_location,
                   d.donor_code, d.blood_type, d.ethnicity, d.education_level
            FROM sperm_vials sv
            JOIN donors d ON sv.donor_id = d.donor_id
            WHERE sv.status = 'Available'
            AND sv.quarantine_release_date <= CURRENT_DATE
            AND d.is_active = true
            ORDER BY sv.vial_id ASC
        """
        
        vials = execute_query(query)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': vials,
            'count': len(vials)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/stats', methods=['GET'])
def get_vial_stats():
    """
    Get vial statistics
    GET /api/sperm-vials/stats
    """
    try:
        query = """
            SELECT 
                COUNT(*) as total_vials,
                COUNT(*) FILTER (WHERE status = 'Available') as available_vials,
                COUNT(*) FILTER (WHERE status = 'Reserved') as reserved_vials,
                COUNT(*) FILTER (WHERE status = 'Used') as used_vials,
                AVG(motility_percent) as avg_motility,
                AVG(sperm_count_mil) as avg_count
            FROM sperm_vials
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

